package io.xmu.dataanalysis.repository;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.Date;
import java.util.List;

import io.xmu.dataanalysis.entity.Order;

public interface OrderRepo extends PagingAndSortingRepository<Order, Integer> {


  List<Order> findOrderByStatus(Integer status);

  List<Order> findByCreateTimeAfterAndCreateTimeBefore(Date startTime, Date endTime);

  @Query(value = " SELECT * FROM `order` WHERE GoodsId in  " +
          " ( SELECT Id FROM goods WHERE ShopId = ?1 ) " +
          " AND CreateTime > ?2 " +
          " AND CreateTime <= ?3 ",nativeQuery = true)
  List<Order> findByShopIdAndCreateTime(Integer shopId,String start,String end);


  @Query(value = "SELECT COUNT(DISTINCT UserId) FROM `order` WHERE GoodsId IN " +
          " (SELECT Id FROM goods WHERE ShopId = ?1 ) " +
          " AND CreateTime >= ?2 " +
          " AND CreateTime < ?3 " +
          " AND `Status` IN (100,200)",nativeQuery = true)
  Integer findOrderUserCountByShopIdAnAndCreateTime(Integer shopId,String start,String end);

  @Query(value = " SELECT DISTINCT(`UserId`) FROM `order` as table1 \n" +
          "WHERE \n" +
          "`Status` > 100\n" +
          "AND `CreateTime` > ?2\n" +
          "AND `CreateTime` < ?3\n" +
          "AND `GoodsId` IN (\n" +
          "SELECT Id FROM goods WHERE `ShopId`=?1\n" +
          ")",nativeQuery = true)
  List<Integer> findCustomerByShopIdAndCreateTime(Integer shopId,String start,String end);


}
